
import mysql.connector,os,shutil
import z_combine_images


def get_column_value(column_name, condition_field=None, condition_value=None):
    try:
        # 1. 连接数据库
        conn = mysql.connector.connect(
            host="192.168.10.81",
            user="root",
            passwd="",
            database="xiaocheng"
        )
        cursor = conn.cursor()

        # 2. 构建 SQL 查询（参数化防止 SQL 注入）
        if condition_field and condition_value:
            sql = f"SELECT {column_name} FROM data_model WHERE {condition_field} = %s"
            params = (condition_value,)
        else:
            sql = f"SELECT {column_name} FROM data_model LIMIT 1"
            params = ()

        # 3. 执行查询并获取结果
        cursor.execute(sql, params)
        result = cursor.fetchone()  # 提取第一行

        if result:
            return result[0]  # 返回目标列的值
        else:
            return None  # 无匹配数据

    except mysql.connector.Error as e:
        print(f"数据库错误: {e}")
        return None

    finally:
        # 4. 关闭连接
        if conn.is_connected():
            cursor.close()
            conn.close()
import mysql.connector

def get_column_values(column_name, condition_field=None, condition_value=None):
    values = []
    try:
        # 1. 连接数据库
        conn = mysql.connector.connect(
            host="192.168.10.81",
            user="root",
            passwd="",
            database="xiaocheng"
        )
        cursor = conn.cursor()

        # 2. 动态构建 SQL（支持无条件或按条件查询）
        if condition_field and condition_value:
            sql = f"SELECT {column_name} FROM data_kuaimai_guangban WHERE {condition_field} = %s"
            params = (condition_value,)
        else:
            sql = f"SELECT {column_name} FROM data_kuaimai_guangban"
            params = ()

        # 3. 执行查询并获取结果
        cursor.execute(sql, params)
        results = cursor.fetchall()  # 获取所有匹配行（格式如 [('值1',), ('值2',)]）

        # 4. 提取目标列的值到列表
        if results:
            values = [row[0] for row in results]

    except mysql.connector.Error as e:
        print(f"数据库错误: {e}")

    finally:
        # 5. 关闭连接
        if conn.is_connected():
            cursor.close()
            conn.close()
        return values

def get_paths(directory):
    paths = []
    for file in os.listdir(directory):
        path=os.path.join(directory,file)
        if os.path.isdir(path):
            path=path.replace('\\','/')
            paths.append(path)
    return paths

import mysql.connector

def get_data_count():
    data_count = None
    try:
        # 1. 连接数据库
        conn = mysql.connector.connect(
            host="192.168.10.81",
            user="root",
            passwd="",
            database="xiaocheng"
        )
        cursor = conn.cursor()

        # 2. 执行查询最大 id 的 SQL
        sql = "SELECT count(*) FROM data_kuaimai_operater_number"
        cursor.execute(sql)
        result = cursor.fetchone()  # 返回格式如 (1005, )

        # 3. 处理结果（可能为 None，若表为空）
        if result and result[0] is not None:
            data_count = result[0]
        else:
            data_count = 0  # 或根据需求返回 None

    except mysql.connector.Error as e:
        print(f"数据库错误: {e}")
        data_count = None

    finally:
        # 4. 关闭连接
        if conn.is_connected():
            cursor.close()
            conn.close()
        return data_count
def clear_folder(folder_path):
    """
    清空指定文件夹内的所有文件和子文件夹，通过删除并重新创建文件夹实现。

    :param folder_path: 要清空的文件夹路径
    """
    try:
        shutil.rmtree(folder_path)  # 删除文件夹及其内容
        os.makedirs(folder_path)   # 重新创建空文件夹
    except Exception as e:
        print(f"Failed to clear {folder_path}. Reason: {e}")

clear_folder("D:/Desk/TEMU-批量上款/3-上架图包")
clear_folder("D:/Desk/TEMU-批量上款/2-快麦图包")
clear_folder("D:/Desk/TEMU-批量上款/4-错误")


mydb = mysql.connector.connect(
  host="192.168.10.81",
  user="root",
  passwd="",
  database="xiaocheng"
)
mycursor = mydb.cursor()

paths= get_paths("D:/Desk/TEMU-批量上款/1-内容分组")

for path in paths:
  pics=os.listdir(path)
  # 文件数量验证
  values=['主图','方图','前','后','详情1','详情2','pic']
  for value in values:
    tag=0
    for pic in pics:
      if value in pic:
        tag=1
    if tag==0:
      path_new="D:/Desk/TEMU-批量上款/4-错误/"+'文件缺失#'+path.split('/')[-1]
      shutil.copy(path,path_new)
  # 模特款号与快卖SKU验证
  model_name=(path.split('/')[-1]).split('_')[0]
  style_name=get_column_value("style_name", "name", model_name)
  if style_name==None:
    path_new="D:/Desk/TEMU-批量上款/4-错误/"+'款号错误#'+path.split('/')[-1]
    shutil.copy(path,path_new)
  else:
    skus=get_column_values("km_number", "style_name", style_name)
    if len(skus)==0:
      path_new="D:/Desk/TEMU-批量上款/4-错误/"+'快卖错误#'+path.split('/')[-1]
      shutil.copy(path,path_new)
    else:
      operater_number='A'+str(50000+get_data_count()+1)
      sql = "INSERT INTO data_kuaimai_operater_number(operater_number) VALUES (%s)"
      val = (operater_number,)
      mycursor.execute(sql, val) 
      mydb.commit()
      # 移动文件到上架图包
      for pic in pics:
        if 'pic' not in pic:
          if not os.path.exists("D:/Desk/TEMU-批量上款/3-上架图包/"+operater_number):
            os.makedirs("D:/Desk/TEMU-批量上款/3-上架图包/"+operater_number)
          path_new="D:/Desk/TEMU-批量上款/3-上架图包/"+operater_number+'/'+pic
          shutil.copy(path+'/'+pic,path_new)
        else:
          if not os.path.exists("D:/Desk/TEMU-批量上款/2-快麦图包/"+style_name.split('#')[0]):
            os.makedirs("D:/Desk/TEMU-批量上款/2-快麦图包/"+style_name.split('#')[0])
          path_new="D:/Desk/TEMU-批量上款/2-快麦图包/"+style_name.split('#')[0]+'/'+operater_number+'.png'
          shutil.copy(path+'/'+pic,path_new)
      path_front=path+'/'+'前.jpg'
      path_back=path+'/'+'后.jpg'
      path_result="D:/Desk/TEMU-批量上款/2-快麦图包/"+operater_number+'#'+style_name.split('#')[1]+'.jpg'
      try:
        z_combine_images.combine_images_with_title(path_front,path_back,path_result)
        for sku in skus:
          path_new="D:/Desk/TEMU-批量上款/2-快麦图包/"+style_name.split('#')[0]+'/'+"（图）"+sku+"+"+operater_number+'.jpg'
          shutil.copy(path_result,path_new)
      except:
        print(os.basename(path),'合成失败')
      os.remove(path_result)